/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package Clases;

import java.util.LinkedList;
import java.sql.*;
import java.util.Date;
/**
 *
 * @author TVD
 */
public class AccesoDatos {
        private LinkedList < Tarea > mTareas = new LinkedList<>(); 
        private ListaCategoria listaCat = new ListaCategoria();
        private ListaTareas listaTarea=new ListaTareas();
        private ListaResponsable listaRes = new ListaResponsable();
public ListaTareas getTareas(ListaCategoria lc, ListaResponsable lr){
      listaRes = lr;
      listaCat = lc;
     ListaTareas aux = new ListaTareas();
    try{
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:ManagerDb";
        Connection con = DriverManager.getConnection(url);
        Statement s = con.createStatement();
        ResultSet rs = s.executeQuery("select * from tarea");
        
        while(rs.next())
        {
              Tarea tarea = new Tarea (rs.getInt("id"), 
                                        rs.getString("titulo"), 
                                        rs.getString("texto"), 
                                        rs.getInt("prioridad"), 
                                        rs.getDate("vencimiento"), 
                                       listaRes.buscar( rs.getInt("id_responsable")), 
                                       listaCat.buscar( rs.getInt("id_categoria")), 
                                        rs.getDouble("completado"));              
              aux.agregar(tarea);
        }
        rs.close();
        s.close();
        con.close();
        
    }    
    
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
    
     listaTarea = aux;
    return listaTarea;
}
public ListaTareas getTareas(ListaCategoria lc, ListaResponsable lr, String titulo, int prioridad, int Responsable,int Categoria, Date fecha)
{
    listaRes = lr;
    listaCat = lc;
     ListaTareas aux = new ListaTareas();
    try{
         java.sql.Date sqlDate=new java.sql.Date(fecha.getTime());
         
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:ManagerDb";
        Connection con = DriverManager.getConnection(url);
        Statement s = con.createStatement();
        String cad = "select tarea.id, titulo, texto, prioridad, vencimiento, id_responsable, id_categoria, completado from ((tarea left join responsable on (tarea.id_responsable = responsable.id)) left join categoria on (tarea.id_categoria = categoria.id))  where";
        if(!"".equals(titulo))cad += " titulo LIKE '%"+titulo+"%' AND";
        if(prioridad != 0) cad += " tarea.prioridad = "+prioridad+" AND";
        if(Responsable != 0) cad += " responsable.id = "+Responsable+" AND";
        if(Categoria != 0) cad += " categoria.id = "+Categoria+" AND";
        cad += " vencimiento < '"+sqlDate+"'";
        
        PreparedStatement ps = con.prepareStatement(cad);       

        ResultSet rs = ps.executeQuery();
        
        while(rs.next())
        {
              Tarea tarea = new Tarea (rs.getInt("id"), 
                                        rs.getString("titulo"), 
                                        rs.getString("texto"), 
                                        rs.getInt("prioridad"), 
                                        rs.getDate("vencimiento"), 
                                       listaRes.buscar( rs.getInt("id_responsable")), 
                                       listaCat.buscar( rs.getInt("id_categoria")), 
                                        rs.getDouble("completado"));              
              aux.agregar(tarea);
        }
        rs.close();
        s.close();
        con.close();
        
    }    
    
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
    
     listaTarea = aux;     
    return listaTarea;

}
//public void mimetodo()
//{
//
//    try{
//         Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
//        String url = "jdbc:derby:ManagerDb";
//        Connection con = DriverManager.getConnection(url);
//        Statement s = con.createStatement();
//        
//        PreparedStatement ps = con.prepareStatement("update responsable set path_foto = 'default.jpg'");  
//       ps.executeUpdate();
//    
//        s.close();
//        con.close();
//    }
//     catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
//
//}

public ListaCategoria getListaCategoria()
        {
            try{
      
                
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:ManagerDb";
        Connection con = DriverManager.getConnection(url);
        Statement s = con.createStatement();
        ResultSet rs = s.executeQuery("select * from categoria");
        listaCat.agregar(new Categoria(0,"Todas las Categorias","Todas"));
        while(rs.next())
        {
               Categoria categoria = new Categoria(rs.getInt("id"), rs.getString("nombre"), rs.getString("descripcion"));
               listaCat.agregar(categoria);
        }
        rs.close();
        s.close();
        con.close();
        
    }
    
    
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
    return listaCat;
}

public ListaResponsable getListaResponsable()
{
    
      try{
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:ManagerDb";
        Connection con = DriverManager.getConnection(url);
        Statement s = con.createStatement();
        ResultSet rs = s.executeQuery("select * from responsable");
        listaRes.agregar(new Responsable(0,"Todos","Todos","",""));
        while(rs.next())
        {
               
               Responsable responsable = new Responsable(rs.getInt("id"), rs.getString("nombre"), rs.getString("apodo"), rs.getString("email"), rs.getString("path_foto"));
               listaRes.agregar(responsable);
        }
        rs.close();
        s.close();
        con.close();
        
    }
        
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
return listaRes;    
}

public int obtenerMayor(String nombreTabla)
{
        int id=0;
    
      try{
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:ManagerDb";
        Connection con = DriverManager.getConnection(url);
        Statement s = con.createStatement();
        ResultSet rs = s.executeQuery("select max(id) as id from "+nombreTabla);
        while(rs.next())
        {
            id=rs.getInt("id");
        }
        
        
       
        rs.close();
        s.close();
        con.close();
        
    }
        
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
return id;    

}
    
public int insert(Object o)
{
    int id=0;
     if(o instanceof Categoria)
     {
         Categoria c=(Categoria)o;
         try{
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:ManagerDb";
        Connection con = DriverManager.getConnection(url);
        PreparedStatement ps=con.prepareStatement("INSERT INTO categoria VALUES (?,?,?)");
        ps.setInt(1, c.getId());
        ps.setString(2, c.getNombre());
        ps.setString(3, c.getDescripcion());
        ps.executeUpdate();
        ps.close();
        con.close();
        id=c.getId();
    }
        
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
    }
     
    if(o instanceof Responsable)
    {
        Responsable r=(Responsable)o;
    try{
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:ManagerDb";
        Connection con = DriverManager.getConnection(url);
        PreparedStatement ps=con.prepareStatement("INSERT INTO responsable VALUES (?,?,?,?,?)");
        ps.setInt(1, r.getId());
        ps.setString(2, r.getNombre());
        ps.setString(3, r.getApodo());
        ps.setString(4, r.getEmail());
        ps.setString(5, r.getPathFoto());
        ps.executeUpdate();
        ps.close();
        con.close();
        id=r.getId();
        
    }
        
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
 
    }
    
    if(o instanceof Tarea)
    {
        Tarea t=(Tarea)o;
        java.sql.Date sqlDate=new java.sql.Date(t.getVencimiento().getTime());
    try{
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:ManagerDb";
        Connection con = DriverManager.getConnection(url);
        PreparedStatement ps=con.prepareStatement("INSERT INTO tarea VALUES (?,?,?,?,?,?,?,?)");
        ps.setInt(1, t.getId());
        ps.setString(2, t.getTitulo());
        ps.setString(3, t.getTexto());
        ps.setInt(4, t.getPrioridad());
        ps.setDate(5, sqlDate);
        ps.setInt(6, t.getResponsable().getId());
        ps.setInt(7, t.getCategoria().getId());
        ps.setDouble(8, t.getCompletado());
        ps.executeUpdate();
        ps.close();
        con.close();
        id=t.getId();
    }
        
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
    }
    
    return id;
        
}

public int delete(Object o)
{
    int encontrado=0;
    if (o instanceof Categoria)
    {
        
         Categoria c=(Categoria)o;
         try{
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:ManagerDb";
        Connection con = DriverManager.getConnection(url);
        PreparedStatement ps=con.prepareStatement("DELETE FROM categoria where id=?");
        ps.setInt(1, c.getId());
        ps.executeUpdate();
        ps.close();
        con.close();
        encontrado=c.getId();
    }
        
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
    
    }
    
    if(o instanceof Responsable)
    {
        
        Responsable r=(Responsable)o;
        try{
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
            String url = "jdbc:derby:ManagerDb";
            Connection con = DriverManager.getConnection(url);
            PreparedStatement ps=con.prepareStatement("DELETE FROM responsable where id=?");
            ps.setInt(1, r.getId());
            ps.executeUpdate();
            ps.close();
            con.close();
            encontrado=r.getId();
        
    }
        
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
    }
    
    if (o instanceof Tarea)
    {
        Tarea t=(Tarea)o;
        try{
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:ManagerDb";
        Connection con = DriverManager.getConnection(url);
        PreparedStatement ps=con.prepareStatement("DELETE FROM tarea where id=?");
        ps.setInt(1, t.getId());
        ps.executeUpdate();
        ps.close();
        con.close();
        encontrado=t.getId();
    }
        
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
    }
    
    return encontrado;
}

    public int update(Object o)
    {
        int id=0;
        if(o instanceof Categoria)
     {
         Categoria c=(Categoria)o;
         try{
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:ManagerDb";
        Connection con = DriverManager.getConnection(url);
        PreparedStatement ps=con.prepareStatement("UPDATE categoria set nombre=?, descripcion=? WHERE id=?");
        ps.setInt(3, c.getId());
        ps.setString(1, c.getNombre());
        ps.setString(2, c.getDescripcion());
        ps.executeUpdate();
        ps.close();
        con.close();
        id=c.getId();
    }
        
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
    }
     
    if(o instanceof Responsable)
    {
        Responsable r=(Responsable)o;
    try{
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:ManagerDb";
        Connection con = DriverManager.getConnection(url);
        PreparedStatement ps=con.prepareStatement("UPDATE responsable set nombre=?,apodo=?,email=?,path=? where id=?");
        ps.setInt(5, r.getId());
        ps.setString(1, r.getNombre());
        ps.setString(2, r.getApodo());
        ps.setString(3, r.getEmail());
        ps.setString(4, r.getPathFoto());
        ps.executeUpdate();
        ps.close();
        con.close();
        id=r.getId();
        
    }
        
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
 
    }
    
    if(o instanceof Tarea)
    {
        Tarea t=(Tarea)o;
        java.sql.Date sqlDate=new java.sql.Date(t.getVencimiento().getTime());
    try{
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:ManagerDb";
        Connection con = DriverManager.getConnection(url);
        PreparedStatement ps=con.prepareStatement("UPDATE tarea set titulo=?,texto=?,prioridad=?,vencimiento=?,id_responsable=?,id_categoria=?,completado=? where id=?");
        ps.setInt(8, t.getId());
        ps.setString(1, t.getTitulo());
        ps.setString(2, t.getTexto());
        ps.setInt(3, t.getPrioridad());
        ps.setDate(4, sqlDate);
        ps.setInt(5, t.getResponsable().getId());
        ps.setInt(6, t.getCategoria().getId());
        ps.setDouble(7, t.getCompletado());
        ps.executeUpdate();
        ps.close();
        con.close();
        id=t.getId();
    }
        
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
    }
    
    return id;
    }
    public boolean responsableTieneTareas(int id)
    {
        boolean b = false;
         try{
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:ManagerDb";
        Connection con = DriverManager.getConnection(url);
        Statement s = con.createStatement();
        ResultSet rs = s.executeQuery("select * from tarea join responsable on tarea.id_responsable = responsable.id where responsable.id = "+id);
        
        b = !rs.next();
        
        rs.close();
        s.close();
        con.close();
        
    }    
    
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
    return b;
    }
    public boolean categoriaTieneTareas(int id)
    {
        boolean b = false;
         try{
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:ManagerDb";
        Connection con = DriverManager.getConnection(url);
        Statement s = con.createStatement();
        ResultSet rs = s.executeQuery("select * from tarea join categoria on tarea.id_categoria = categoria.id where categoria.id = "+id);
        
        b = !rs.next();
        
        rs.close();
        s.close();
        con.close();
        
    }    
    
    catch (SQLException | ClassNotFoundException e){ e.printStackTrace();}
    return b;
    }
}





    




